package art.server.db;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.NoResultException;
import javax.persistence.Persistence;

import art.server.db.entities.Art;
import art.server.db.entities.Genre;
import art.server.db.entities.PortalUser;
import art.server.db.entities.Rating;

public class DatabaseHandler {

	private static DatabaseHandler instance = null;
	private final String db = "art-portal-database";
	private EntityManagerFactory factory = Persistence
			.createEntityManagerFactory(db);

	private DatabaseHandler() {
	}

	public static DatabaseHandler getInstance() {
		if (instance == null) {
			instance = new DatabaseHandler();
		}
		return instance;
	}

	public List<Art> getAllArts() {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		List<Art> arts = null;
		try {
			transaction.begin();
			arts = manager.createQuery("SELECT a FROM Art a", Art.class)
					.getResultList();
			if (arts != null) {
				for (Art art:arts) {
					art.setRatings(getRatingsByArt(art.getId()));
				}
			}
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
		}
		manager.close();
		return arts;
	}

	public List<PortalUser> getAllUsers() {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		List<PortalUser> users = null;
		try {
			transaction.begin();
			users = manager.createQuery("SELECT u FROM PortalUser u",
					PortalUser.class).getResultList();
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
		}
		manager.close();
		return users;
	}

	public List<Rating> getAllRatings() {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		List<Rating> ratings = null;
		try {
			transaction.begin();
			ratings = manager.createQuery("SELECT r FROM Rating r",
					Rating.class).getResultList();
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
		}
		manager.close();
		return ratings;
	}

	public List<Genre> getAllGenres() {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		List<Genre> genres = null;
		try {
			transaction.begin();
			genres = manager.createQuery("SELECT g FROM Genre g", Genre.class)
					.getResultList();
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
		}
		manager.close();
		return genres;
	}

	public List<Art> getArtsByUser(long userId) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		List<Art> arts = null;
		try {
			transaction.begin();
			arts = manager
					.createQuery(
							"SELECT a FROM Art a WHERE a.creatorId = :user",
							Art.class).setParameter("user", userId)
					.getResultList();
			if (arts != null) {
				for (Art art:arts) {
					art.setRatings(getRatingsByArt(art.getId()));
				}
			}
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
		}
		manager.close();
		return arts;

	}

	public List<Art> getArtsByGenre(Genre genre) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		List<Art> arts = null;
		try {
			transaction.begin();
			arts = manager
					.createQuery("SELECT a FROM Art a WHERE a.genre = :genre",
							Art.class).setParameter("genre", genre)
					.getResultList();
			if (arts != null) {
				for (Art art:arts) {
					art.setRatings(getRatingsByArt(art.getId()));
				}
			}
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
		}
		manager.close();
		return arts;
	}

	public Art getArt(long artId) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		Art art = null;
		try {
			transaction.begin();
			art = manager
					.createQuery("SELECT a FROM Art a WHERE a.id = :artId",
							Art.class).setParameter("artId", artId)
					.getSingleResult();
			if (art != null) {
				art.setRatings(getRatingsByArt(art.getId()));
			}
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
		}
		manager.close();
		return art;
	}

	public List<Rating> getRatingsByArt(long artId) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		List<Rating> ratings = null;
		try {
			transaction.begin();
			ratings = manager
					.createQuery("SELECT r FROM Rating r WHERE r.artId = :art",
							Rating.class).setParameter("art", artId)
					.getResultList();
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
		}
		manager.close();
		return ratings;
	}

	public PortalUser getUserByName(String name) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		PortalUser user = null;
		try {
			transaction.begin();
			user = manager
					.createQuery(
							"SELECT u FROM PortalUser u WHERE u.name = :name",
							PortalUser.class).setParameter("name", name)
					.getSingleResult();
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
		}
		manager.close();
		return user;
	}

	public PortalUser getUserByEmail(String email) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		PortalUser user = null;

		try {
			transaction.begin();
			user = manager
					.createQuery(
							"SELECT u FROM PortalUser u WHERE u.email = :email",
							PortalUser.class).setParameter("email", email)
					.getSingleResult();
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
		}
		manager.close();
		return user;
	}

	public PortalUser getUserByNickname(String nickname) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		PortalUser user = null;
		try {
			transaction.begin();
			user = manager
					.createQuery(
							"SELECT u FROM User u WHERE u.nickname = :name",
							PortalUser.class).setParameter("name", nickname)
					.getSingleResult();
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
		}
		manager.close();
		return user;
	}

	public Genre getGenreByName(String name) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		Genre genre = null;
		try {
			transaction.begin();
			genre = manager
					.createQuery("SELECT g FROM Genre g WHERE g.name = :name",
							Genre.class).setParameter("name", name)
					.getSingleResult();
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
		}
		manager.close();
		return genre;
	}

	public boolean removeUser(long userId) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		try {
			transaction.begin();
			try {
				manager.createQuery("DELETE FROM PortalUser WHERE id = :id",
						PortalUser.class).setParameter("id", userId)
						.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			}
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
			manager.close();
			return false;
		}
		manager.close();
		return true;
	}

	public boolean removeArt(long artId) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		try {
			transaction.begin();
			try {
				manager.createQuery("DELETE FROM Art WHERE id = :id", Art.class)
						.setParameter("id", artId).executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			}
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
			manager.close();
			return false;
		}
		manager.close();
		return true;
	}

	public boolean removeRating(long ratingId) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		try {
			transaction.begin();
			try {
				manager.createQuery("DELETE FROM Rating WHERE id = :id",
						Rating.class).setParameter("id", ratingId)
						.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			}
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
			manager.close();
			return false;
		}
		manager.close();
		return true;
	}

	public boolean removeGenre(long genreId) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		try {
			transaction.begin();
			try {
				manager.createQuery("DELETE FROM Genre WHERE id = :id",
						Genre.class).setParameter("id", genreId)
						.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			}
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
			manager.close();
			return false;
		}
		manager.close();
		return true;
	}

	public boolean saveUser(PortalUser user) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		try {
			transaction.begin();
			if (user.getId() != 0) {
				PortalUser dbUser = null;
				try {
					dbUser = manager
							.createQuery(
									"SELECT u FROM PortalUser u WHERE u.id = :id",
									PortalUser.class)
							.setParameter("id", user.getId()).getSingleResult();
				} catch (NoResultException e) {
				}
				if (dbUser != null) {
					dbUser.setArts(user.getArts());
					dbUser.setName(user.getName());
					dbUser.setNickname(user.getNickname());
					dbUser.setGender(user.getGender());
					dbUser.setDateOfBirth(user.getDateOfBirth());
				} else {
					dbUser = user;
				}
				manager.persist(dbUser);
			} else {
				manager.persist(user);
			}
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
			manager.close();
			return false;
		}
		manager.close();
		return true;
	}

	public boolean saveArt(Art art) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		try {
			transaction.begin();
			if (art.getId() != 0) {
				Art dbArt = null;
				try {
					dbArt = manager
							.createQuery(
									"SELECT a FROM Art a WHERE a.id = :id",
									Art.class).setParameter("id", art.getId())
							.getSingleResult();
				} catch (NoResultException e) {
				}
				if (dbArt != null) {
					dbArt.setTitle(art.getTitle());
					dbArt.setGenre(art.getGenre());
					dbArt.setRatings(art.getRatings());
				} else {
					dbArt = art;
				}
				manager.persist(dbArt);
			} else {
				manager.persist(art);
			}
			transaction.commit();
		} catch (Exception e) {
			e.printStackTrace();
			transaction.rollback();
			manager.close();
			return false;
		}
		manager.close();
		return true;
	}

	public boolean saveRating(Rating rating) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		try {
			transaction.begin();
			if (rating.getId() != 0) {
				Rating dbRating = null;
				try {
					dbRating = manager
							.createQuery(
									"SELECT r FROM Rating r WHERE r.id = :id",
									Rating.class)
							.setParameter("id", rating.getId())
							.getSingleResult();
				} catch (NoResultException e) {
				}
				if (dbRating != null) {
					dbRating.setRate(rating.getRate());
					dbRating.setReview(rating.getReview());
				} else {
					dbRating = rating;
				}
				manager.persist(dbRating);
			} else {
				manager.persist(rating);
			}

			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
			manager.close();
			return false;
		}
		manager.close();
		return true;
	}

	public boolean saveGenre(Genre genre) {
		EntityManager manager = factory.createEntityManager();
		EntityTransaction transaction = manager.getTransaction();
		try {
			transaction.begin();
			if (genre.getId() != 0) {
				Genre dbGenre = null;
				try {
					dbGenre = manager
							.createQuery(
									"SELECT g FROM Genre g WHERE g.id = :id",
									Genre.class)
							.setParameter("id", genre.getId())
							.getSingleResult();
				} catch (NoResultException e) {
				}
				if (dbGenre != null) {
					dbGenre.setName(genre.getName());
					dbGenre.setParentId(genre.getParentId());
				} else {
					dbGenre = genre;
				}
				manager.persist(dbGenre);
			} else {
				manager.persist(genre);
			}
			transaction.commit();
		} catch (Exception e) {
			transaction.rollback();
			manager.close();
			return false;
		}
		manager.close();
		return true;
	}
}
